const connection = require("../app/database.js");
// 所有和数据库相关的操作
class MomentServer {
    // 发表动态 
	async create(user_id, title, content , redient = null) {
		const statement = `INSERT INTO moment (user_id, title, content, redient) VALUES (?, ?, ? , ?);`;
        const [result] = await connection.execute(statement, [
            user_id,
            title,
            content,
            redient
        ]);
        return result;
	}


    // 查看用户动态
    async getUserMomentServer (user_id){
        const statement = `SELECT 
                m.id id,m.user_id user_id, m.title title , m.content content , m.redient redient , m.updateAt updateTime ,
                ( SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT(
                        "id" , p.id , "img_path" , p.filename
                    ))
                    FROM photo p WHERE p.moment_id = m.id 
                ) imgList ,
                (SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT("user_id" , upvote.user_id))
                    FROM upvote  WHERE upvote.moment_id = m.id 
                ) upvoteList ,
                ( SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT(
                        "label_id" , ml.label_id 
                    ))
                    FROM moment_label ml WHERE ml.moment_id = m.id 
                ) labelList 
            FROM moment m WHERE m.user_id = ? ORDER BY id DESC;`
        try {
            const [result] = await connection.execute(statement, [user_id])
            return result
        } catch (error) {
            console.log(error);
        }
    }
    // 查看所有动态
    async allMomentServer (){
        const statement = `SELECT 
                m.id id,m.user_id user_id, m.title title , m.content content , m.redient redient , m.updateAt updateTime ,
                ( SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT(
                        "id" , p.id , "img_path" , p.filename
                    ))
                    FROM photo p WHERE p.moment_id = m.id 
                ) imgList ,
                (SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT("user_id" , upvote.user_id))
                    FROM upvote  WHERE upvote.moment_id = m.id 
                ) upvoteList ,
                ( SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT(
                        "label_id" , ml.label_id 
                    ))
                    FROM moment_label ml WHERE ml.moment_id = m.id 
                ) labelList 
            FROM moment m ORDER BY id DESC;`
        try {
            const [result] = await connection.execute(statement)
            return result
        } catch (error) {
            console.log(error);
        }
    }

    // 获取指定动态
    async momentByIdServer(momentId){
        const statement = `SELECT 
                m.id id,m.user_id user_id, m.title title , m.content content , m.redient redient , m.updateAt updateTime ,
                ( SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT(
                        "id" , p.id , "img_path" , p.filename
                    ))
                    FROM photo p WHERE p.moment_id = m.id 
                ) imgList ,
                (SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT("user_id" , upvote.user_id))
                    FROM upvote  WHERE upvote.moment_id = m.id 
                ) upvoteList ,
                ( SELECT 
                    JSON_ARRAYAGG(JSON_OBJECT(
                        "label_id" , ml.label_id 
                    ))
                    FROM moment_label ml WHERE ml.moment_id = m.id 
                ) labelList 
            FROM moment m WHERE m.id = ? ORDER BY id DESC;`
        try {
            const [result] = await connection.execute(statement, [momentId])
            return result[0]
        } catch (error) {
            console.log(error);
        }
    }

    // 查看动态步骤
    async getStepsServer(momentId){
        const statement = `SELECT * FROM steps WHERE steps.moment_id = ?;`
        const [result] = await connection.execute(statement ,[momentId])
        return result
    }

    // 新增步骤 (单个)
    async addStepsServer(moment_id , user_id , content){
        const statement = `INSERT INTO steps ( moment_id, user_id, content) VALUES (?, ?, ?);`
        const [result] = await connection.execute(statement , [moment_id , user_id , content ])
        return result
    }

    // 批量新增步骤 不单独提供给前端 和 发表动态 一起调用
    async addStepsMany(data){
        const statement = 'INSERT INTO steps (moment_id , user_id ,content) VALUES ?;'
        try {
            const [resulst] = await connection.query(statement , [data])
            return resulst
        } catch (error) {
            console.log(error);
        }
        
    }


    // 查看标签
    async getLabelServer(){
        const statement = ` SELECT l.name name , l.id id FROM label l ORDER BY id DESC ;`
        const [result] = await connection.execute(statement)
        return result
    }
    // 验证是否点赞
    async verifyUpvoteByUser(user_id, moment_id){
        const statement = `SELECT * FROM upvote WHERE upvote.user_id = ? AND upvote.moment_id = ?;`
        const [result] = await connection.execute(statement, [user_id, moment_id])
        return !!result.length
    }
    // 为动态点赞
    async upvoteServer(user_id , moment_id){
        const statement = `INSERT INTO upvote (user_id, moment_id) VALUES (?, ?);`
        const [result] = await connection.execute(statement, [user_id , moment_id])
        return result
    }


}
module.exports = new MomentServer();
